package cn.wzvtc.ch11query.repository;

import cn.wzvtc.ch11query.entity.User;
import cn.wzvtc.ch11query.vo.UserDepartVo;
import cn.wzvtc.ch11query.vo.UserVo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {


//    @Query("from User where userName = ?1")
//    List<User> getByName(String userName);

//    @Query("select code from User where userName = ?1")
//    List<String> getByName(String userName);


    //根据用户名查询且只输出code字段
    @Query("select new cn.wzvtc.ch11query.vo.UserVo(u.code, u.userName, u.gender)" +
            " from User u where u.userName = ?1")
    List<UserVo> getByName(String userName);


//    @Query("from User where userName = :un ")
//    List<User> getByName(@Param("un") String userName);

    @Transactional//事务：原子性，回滚
    @Modifying
    @Query("update User set gender=:gender where userName=:name ")
    int updateGender(@Param("name") String userName, @Param("gender") Integer gender);


    //查询所有用户的code，姓名，性别，部门名称
    @Query("select new cn.wzvtc.ch11query.vo.UserDepartVo(u.code, u.userName, u.gender, d.name) " +
            "from User u, Department d where u.departmentId = d.id")
    List<UserDepartVo> getUserDepartment();

    //原生SQL语句查询所有用户
    @Query(value = "select * from user where user_name = ?1", nativeQuery = true)
    List<User> getByName3(String userName);

    @Query(value = "select *  from User WHERE gender = ?1", nativeQuery = true)
    Page<User> findPageByGender(Integer gender, Pageable page);

    @Query(value = "select u from User u  WHERE u.gender = ?1")
    List<User> findByGenderSort(Integer gender, Sort sort);

}
